package edu.tms.dao;

import java.util.List;
import java.util.Map;

import edu.tms.bean.Tuition;
import edu.tms.crud.JdbcUtil;
import edu.tms.dao.common.CommnonDao;

/**
 * 个人缴费dao
 * @author Administrator
 *
 */
public class TuitionDaoImp  implements CommnonDao<Tuition>{
	/**
	 * 保存内容
	 * 返回该条记录的自增长值（id）
	 * @param value
	 * @throws Exception
	 */
	@Override
	public long save(Tuition value) throws Exception {
		String sql="INSERT INTO t_tuition(stu_id,tuition_content,";
		sql+= "tuition_time,tuition_sum,create_time,tuition_state) VALUES(?,?,?,?,?,1)";
		long Number=JdbcUtil.insertAuto(sql, value.getStuId(),
				value.getTuitionContent(),
				value.getTuitionTime(),
				value.getTuitionSum(),
				value.getCreateTime()
				);
		return Number;
	}
	/**
	 * 修改内容
	 * @param value
	 * @throws Exception
	 */
	@Override
	public int update(Tuition value) throws Exception {
		Tuition tuition=null;
		tuition=(Tuition)value;
		String sql="UPDATE t_tuition SET stu_id=?,tuition_content=?,tuition_time=?";
		sql+= ",tuition_sum=?,create_time=?,tuition_state=? WHERE tuition_id = ?";
		int Number=JdbcUtil.update(sql,
				tuition.getStuId(),
				tuition.getTuitionContent(),
				tuition.getTuitionTime(),
				tuition.getTuitionSum(),
				tuition.getCreateTime(),
				tuition.getTuitionState(),
				tuition.getTuitionId());
		return Number;
	}
	
	/**
	 * 删除内容
	 * 相关联的表：提交人？
	 * 学生表t_student，教师表t_teacher，
	 * 管理员表t_admin，
	 * @param value
	 * @throws Exception
	 */
	@Override
	public int delete(Tuition value) throws Exception {
		String sql="DELETE FROM t_tuition WHERE stu_id=? AND tuition_content=?";
		int Number=JdbcUtil.update(sql,value.getStuId(),value.getTuitionContent());
		return Number;
	}
	/**
	 * 查找所有的内容
	 *  相关联的表：提交人？ 学生表t_student，教师表t_teacher， 管理员表t_admin，
	 * @param pageNo 第几页
	 * @param pageSize 每页数字
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Tuition> findAll(int pageNo, int pageSize) throws Exception {
		String sql="SELECT tu.stu_id,stu.stu_name,tu.tuition_content,tu.tuition_time,tu.tuition_sum,tu.create_time,tu.tuition_state ";
		sql+="FROM  t_tuition tu,t_student stu ";
		sql+="WHERE tu.stu_id=stu.stu_id LIMIT ?,?";
		return JdbcUtil.query(Tuition.class, sql,(pageNo-1)*pageSize,pageSize);
	}

	/**
	 * String 对应  数据库 列名（comment)
	 * Object 对应相应列名值
	 * 返回的是所查找的类中的数据。方便EXCEL导出
	 */
	@Override
	public List<Map<String,Object>> getMapList() throws Exception {
		// TODO Auto-generated method stub
		return null;
	}
	
	
	@SuppressWarnings("unchecked")
	@Override
	public List<Tuition> findAll(Object key, int pageNo, int pageSize) throws Exception {
		String sql="SELECT tu.tuition_id,tu.stu_id,stu.stu_name,stu.stu_no,tu.tuition_content,tu.tuition_time,tu.tuition_sum,tu.create_time,tu.tuition_state ";
		sql+="FROM  t_tuition tu,t_student stu ";
		sql+="WHERE tu.stu_id=stu.stu_id AND tu.stu_id=? or stu.stu_name=? LIMIT ?,?";
		return JdbcUtil.query(Tuition.class, sql,key,key,(pageNo-1)*pageSize,pageSize);
	}
	@Override
	public Tuition findByKey(Object key) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}
	
	
	/**
	 * 查询总共有多少数据
	 * @throws Exception 
	 */
	public long getCount(Object key,int size) throws Exception{
		String sql="SELECT count(*) ";
		sql+="FROM  t_tuition tu,t_student stu ";
		sql+="WHERE tu.stu_id=stu.stu_id AND tu.stu_id=? or stu.stu_name=?";
		long pages = (long)JdbcUtil.uniqueValue(sql, 1, key, key);
		return pages%size==0?pages/size:pages/size+1;
	}
	/**
	 * 根据辅导员编号查询缴费记录
	 * @param tchId
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<Tuition> queryBytchId(Integer tchId,Integer pageNo,Integer pageSize) throws Exception{
		String sql="SELECT t.tuition_id,s.stu_name,t.tuition_content,t.create_time,t.tuition_sum,t.tuition_state"
				+ " FROM t_tuition AS t"
				+ " LEFT JOIN t_student AS s ON t.stu_id=s.stu_id"
				+ " LEFT JOIN t_class_info AS c ON c.class_name=s.stu_class_name"
				+ " LEFT JOIN t_teacher AS te ON te.tch_id=c.class_tch_id"
				+ " WHERE te.tch_id=? "
				+ " LIMIT ?,?";
		List<Tuition> tuis=JdbcUtil.query(Tuition.class, sql, tchId,(pageNo-1)*pageSize,pageSize);
		return tuis;
	}
	/**
	 * 根据辅导员编号统计缴费记录数据
	 * @param tchId
	 * @return
	 * @throws Exception
	 */
	public long pageBytchId(Integer tchId,Integer pageSize) throws Exception{
		String sql="SELECT count(*)"
				+ " FROM t_tuition AS t"
				+ " LEFT JOIN t_student AS s ON t.stu_id=s.stu_id"
				+ " LEFT JOIN t_class_info AS c ON c.class_name=s.stu_class_name"
				+ " LEFT JOIN t_teacher AS te ON te.tch_id=c.class_tch_id"
				+ " WHERE te.tch_id=?";
		long pages=(long) JdbcUtil.uniqueValue(sql, 1, tchId);
		return pages%pageSize==0?pages/pageSize:pages/pageSize+1;
	}
	/**
	 * 根据根据辅导员编号以及专业和班级查询缴费记录
	 * @param tchId
	 * @param majorName
	 * @param className
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<Tuition> queryByMajor(Integer tchId,String majorName,String className,
			Integer pageNo,Integer pageSize) throws Exception{
		String sql="SELECT t.tuition_id,s.stu_name,t.tuition_content,t.create_time,t.tuition_sum,t.tuition_state"
				+ " FROM t_tuition AS t"
				+ " LEFT JOIN t_student AS s ON t.stu_id=s.stu_id"
				+ " LEFT JOIN t_class_info AS c ON c.class_name=s.stu_class_name"
				+ " LEFT JOIN t_teacher AS te ON te.tch_id=c.class_tch_id"
				+ " WHERE te.tch_id=? and c.class_name=? and c.major_name=?"
				+ " LIMIT ?,?";
		List<Tuition> tuis=JdbcUtil.query(Tuition.class, sql, tchId,className,majorName,(pageNo-1)*pageSize,pageSize);
		return tuis;
	}
	/**
	 * 根据根据辅导员编号以及专业和班级统计缴费记录数据
	 * @param tchId
	 * @param majorName
	 * @param className
	 * @return
	 * @throws Exception
	 */
	public long pageByMajor(Integer tchId,String majorName,String className,Integer pageSize) throws Exception{
		String sql="SELECT count(*)"
				+ " FROM t_tuition AS t"
				+ " LEFT JOIN t_student AS s ON t.stu_id=s.stu_id"
				+ " LEFT JOIN t_class_info AS c ON c.class_name=s.stu_class_name"
				+ " LEFT JOIN t_teacher AS te ON te.tch_id=c.class_tch_id"
				+ " WHERE te.tch_id=? and c.class_name=? and c.major_name=?";
				
		long pages=(long) JdbcUtil.uniqueValue(sql, 1,tchId,className,majorName);
		return pages%pageSize==0?pages/pageSize:pages/pageSize+1;
	}
	/**
	 * 根据根据辅导员编号以及专业和班级和学生姓名统计缴费记录数据
	 * @param tchId
	 * @param majorName
	 * @param className
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws Exception
	 */
	public long pageByStuName(Integer tchId,String majorName,String className,
			String stuName,Integer pageSize) throws Exception{
		String sql="SELECT count(*)"
				+ " FROM t_tuition AS t"
				+ " LEFT JOIN t_student AS s ON t.stu_id=s.stu_id"
				+ " LEFT JOIN t_class_info AS c ON c.class_name=s.stu_class_name"
				+ " LEFT JOIN t_teacher AS te ON te.tch_id=c.class_tch_id"
				+ " WHERE te.tch_id=? and c.class_name=? and s.stu_name=? and c.major_name=?";
		long pages=(long) JdbcUtil.uniqueValue(sql, 1,tchId,className,stuName,majorName);
		return pages%pageSize==0?pages/pageSize:pages/pageSize+1;
	}
	
	/**
	 * 根据根据辅导员编号以及专业和班级和学生姓名查询缴费记录
	 * @param tchId
	 * @param majorName
	 * @param className
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<Tuition> queryByStuName(Integer tchId,String majorName,String className,
			String stuName,Integer pageNo,Integer pageSize) throws Exception{
		String sql="SELECT t.tuition_id,s.stu_name,t.tuition_content,t.create_time,t.tuition_sum,t.tuition_state"
				+ " FROM t_tuition AS t"
				+ " LEFT JOIN t_student AS s ON t.stu_id=s.stu_id"
				+ " LEFT JOIN t_class_info AS c ON c.class_name=s.stu_class_name"
				+ " LEFT JOIN t_teacher AS te ON te.tch_id=c.class_tch_id"
				+ " WHERE te.tch_id=? and c.class_name=? and s.stu_name=? and c.major_name=?"
				+ " LIMIT ?,?";
		List<Tuition> tuis=JdbcUtil.query(Tuition.class, sql, tchId,className,stuName,majorName,(pageNo-1)*pageSize,pageSize);
		return tuis;
	}
	
	
}


